-- @owner: @songjing20
-- @date: 2023-06-07
-- @testpoint: 通过where子句绕过列权限，合理报错

--step1:创建测试用户测试表;expect:成功
create user u_privilege_0012 with password "Schina1234@";
create table t_privilege_0012(id int, body text, title text, last_mod_date date);
insert into t_privilege_0012 values(1, 'China, officially the People''s Republic of China(PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1'),
(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1'),
(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England','2010-1-1');

--step2:授权select列权限;expect:成功
grant select (id) on t_privilege_0012 to u_privilege_0012;

--step3:利用where条件中加入输出函数尝试绕过列权控;expect:越权操作失败
set role u_privilege_0012 password "Schina1234@";
create or replace function raise_demo(id integer, name varchar(100)) returns integer language plpgsql immutable as $$
begin
raise notice 'id :% , name : %', id, name;
return 1;
end;
$$;
/

select id from t_privilege_0012 where raise_demo(1, body) = 1;

--step4:清理环境;expect:成功
reset role;
drop table t_privilege_0012 cascade;
drop user u_privilege_0012 cascade;
